using System;
using System.Data;
using System.Collections;
using CE.Database;
/// <summary>
/// Summary description for UserProfileDB
/// </summary>
/// 
namespace CE.CarInsurance
{
    public class CarDriverDB
    {
        public CarDriverDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public bool UpdateAllStatus(Int64 CarId, Int64 UserId)
        {
            MyDatabase _db = new MyDatabase();
            string strSQL = "Update tblCarDriver Set Status = 'InActive' Where CECarId = @CECarId And RefCEUserId = @RefCEUserId;";
            int _result = 0;
            SortedList _sPara = new SortedList();

            _sPara.Add("@CECarId", CarId);
            _sPara.Add("@RefCEUserId", UserId);

            _result = _db.ExecuteNonSqlStatement(strSQL, _sPara);
            return _result > 0;
        }

        public Int64 Insert(CarDriver cardriver)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarDriverInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@StepId", cardriver.StepId);
            _sPara.Add("@CarDriverId", cardriver.CarDriverId);
            _sPara.Add("@CECarId", cardriver.CECarId);
            _sPara.Add("@Salutation", cardriver.Salutation);
            _sPara.Add("@Surname", cardriver.Surname);
            _sPara.Add("@GivenName", cardriver.GivenName);
            _sPara.Add("@DOB", cardriver.DOB);
            _sPara.Add("@Gender", cardriver.Gender);
            _sPara.Add("@MaritalStatus", cardriver.MaritalStatus);
            _sPara.Add("@Occupation", cardriver.Occupation);
            _sPara.Add("@JobNature", cardriver.JobNature);
            _sPara.Add("@IDType", cardriver.IDType);
            _sPara.Add("@IDNo", cardriver.IDNo);
            _sPara.Add("@YearOfDrivingInSg", cardriver.YearOfDrivingInSg);
            _sPara.Add("@RefCEUserId", cardriver.RerCEUserId);
            _sPara.Add("@ViewedDetailId", cardriver.ViewedDetailId);
            _sPara.Add("@Status", cardriver.Status);
            _sPara.Add("@Info1", cardriver.Info1);
            _sPara.Add("@Info2", cardriver.Info2);
            _sPara.Add("@Info3", cardriver.Info3);
            _sPara.Add("@Info4", cardriver.Info4);
            _sPara.Add("@Info5", cardriver.Info5);
            _sPara.Add("@Info6", cardriver.Info6);
            _sPara.Add("@Info7", cardriver.Info7);
            _sPara.Add("@Info8", cardriver.Info8);
            _sPara.Add("@Info9", cardriver.Info9);
            _sPara.Add("@Info10", cardriver.Info10);
            _sPara.Add("@Info11", cardriver.Info11);
            _sPara.Add("@Info12", cardriver.Info12);
            _sPara.Add("@Info13", cardriver.Info13);
            _sPara.Add("@Info14", cardriver.Info14);
            _sPara.Add("@Info15", cardriver.Info15);
            _sPara.Add("@Info16", cardriver.Info16);
            _sPara.Add("@Info17", cardriver.Info17);
            _sPara.Add("@Info18", cardriver.Info18);
            _sPara.Add("@Info19", cardriver.Info19);
            _sPara.Add("@Info20", cardriver.Info20);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public bool Update()
        {
            return true;
        }

        public bool Delete()
        {
            return true;
        }

        public CarDriverList GetCarDriverList(Int64 CarId, Int64 UserId)
        {
            CarDriverList _cardriverlist = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarDriverSelectItemList";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CarId);
            _spara.Add("@RefCEUserId", UserId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _cardriverlist = new CarDriverList();
                    for (int i = 0; i < _dt.Rows.Count; i++)
                    {
                        CarDriver _cardriver = new CarDriver();
                        _cardriver = FillDataRecord(_dt, i);
                        _cardriverlist.Add(_cardriver);
                    }
                }
            }
            return _cardriverlist;
        }

        public DataTable GetDriverList(Int64 CarId, Int64 UserId)
        {
            DataTable _cardriverlist = new DataTable();
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarDriverSelectItemList";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CarId);
            _spara.Add("@RefCEUserId", UserId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _cardriverlist = _dt;
                    
                }
            }
            return _cardriverlist;
        }

        public CarDriver GetCarDriver(Int64 CarId, Int64 CarDriverId, Int64 UserId)
        {
            CarDriver _cardriver = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarDriverSelectSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CarId);
            _spara.Add("@RefCEUserId", UserId);
            _spara.Add("@CarDriverId", CarDriverId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _cardriver = new CarDriver();
                    _cardriver = FillDataRecord(_dt,0);
                }
            }
            return _cardriver;
        }

        private CarDriver FillDataRecord(DataTable myDataTable, int RowId)
        {
            CarDriver myCarDriver = new CarDriver();
            myCarDriver.CECarId = Convert.ToInt64(myDataTable.Rows[RowId]["CECarID"].ToString());
            myCarDriver.CarDriverId = Convert.ToInt64(myDataTable.Rows[RowId]["CarDriverId"].ToString());
            myCarDriver.Salutation = myDataTable.Rows[RowId]["Salutation"].ToString();
            myCarDriver.Surname = myDataTable.Rows[RowId]["Surname"].ToString();
            myCarDriver.GivenName = myDataTable.Rows[RowId]["GivenName"].ToString();
            myCarDriver.DOB = Convert.ToDateTime(myDataTable.Rows[RowId]["DOB"].ToString());
            myCarDriver.Gender = Convert.ToChar(myDataTable.Rows[RowId]["Gender"].ToString());
            myCarDriver.MaritalStatus = myDataTable.Rows[RowId]["MaritalStatus"].ToString();
            myCarDriver.Occupation = myDataTable.Rows[RowId]["Occupation"].ToString();
            myCarDriver.JobNature = myDataTable.Rows[RowId]["JobNature"].ToString();
            myCarDriver.IDType = myDataTable.Rows[RowId]["IDType"].ToString();
            myCarDriver.IDNo = myDataTable.Rows[RowId]["IDNo"].ToString();
            myCarDriver.YearOfDrivingInSg = Convert.ToByte(myDataTable.Rows[RowId]["YearOfDrivingInSg"].ToString());
            myCarDriver.RerCEUserId = Convert.ToInt64(myDataTable.Rows[RowId]["RefCEUserId"].ToString());
            myCarDriver.Status = myDataTable.Rows[RowId]["Status"].ToString();
            return myCarDriver;
        }
    }
}